from sqlalchemy import create_engine, text,String,Column
from sqlalchemy.orm import sessionmaker
# 创建基类
# 自定义实体类
class UserInfo:
    def __init__(self):
        self.user_id = None
        self.open_id = None
        self.nick_name = None
        self.province = None
        self.city = None
        self.town = None
        self.others = None
        self.user_name = None

    def __repr__(self):
        return (f"UserInfo(user_id={self.user_id}, open_id={self.open_id}, "
                f"nick_name={self.nick_name}, province={self.province}, "
                f"city={self.city}, town={self.town}, others={self.others}, "
                f"user_name={self.user_name})")


# 创建引擎
engine = create_engine('mysql+pymysql://root:root@127.0.0.1/1204')

# 创建会话类
Session = sessionmaker(bind=engine)

pa = {"nick_name":"兰慧"}

av = []
users = []
with engine.connect() as connection:
    stmt = text(
        """
        SELECT
            wu.id AS user_id,
            ua.province AS province,
            ua.city AS city,
            ua.town AS town,
            ua.others AS others,
            ua.user_name AS user_name,
            wu.open_id AS open_id,
            wu.nick_name AS nick_name 
        FROM
            wx_user AS wu
            LEFT JOIN user_address AS ua ON wu.id = ua.user_id 
        WHERE
            wu.nick_name IS NOT NULL
        """
    )
    # result = connection.execute(stmt, pa)
    result = connection.execute(stmt)
    print(result.cursor.description)
    for it in result.cursor.description:
        av.append(it[0])

    for row in result:
        user_info = UserInfo()
        for rc,v in enumerate(av):
            setattr(user_info, v, row[rc])
        users.append(user_info)

    # 将查询结果映射到 UserInfo 对象上
    for user_it in users:
        print(user_it)
    # print(users)
    # for row in result:
